/****** Object: View [dbo].[vw_4_4_1]   Script Date: 2014-01-19 21:47:45 ******/
USE [SHMIS];
GO
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_4_4_1]'))
BEGIN
DROP VIEW [dbo].[vw_4_4_1];
END
GO

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE VIEW dbo.vw_4_4_1
AS
   SELECT year,
          month,
          SUM (house) AS house,
          SUM (equipment) AS equipment,
          SUM (house) + SUM (equipment) AS total_rent,
          SUM (properties) AS properties
     FROM (SELECT SUBSTRING (CAST (Date AS VARCHAR (10)), 1, 4) AS year,
                  SUBSTRING (CAST (Date AS VARCHAR (10)), 6, 2) AS month,
                  CASE Amount_Type WHEN '01' THEN Amount ELSE 0 END AS house,
                  CASE Amount_Type WHEN '02' THEN Amount ELSE 0 END
                     AS equipment,
                  CASE Amount_Type WHEN '03' THEN Amount ELSE 0 END
                     AS properties
             FROM dbo.HD_AccountReceivable AS ar
            WHERE (verified = '1') AND (deleted = '0') AND (audited = '1')) AS a
   GROUP BY year, month
GO

